13. Quiz: WITH

WITH Quizzes

Essentially a WITH statement performs the same task as a Subquery. Therefore, you can write any of the queries we worked with in the "Subquery Mania" using a WITH. That's what you'll do here. Try to perform each of the earlier queries again, but using a WITH instead of a subquery.

Above is the ERD for the database again - it might come in handy as you tackle the quizzes below. You should write your solution as using a WITH statement, not by finding one solution and copying the output. The importance of this is that it allows your query to be dynamic in answering the question - even if the data changes, you still arrive at the right answer.

1. Provide the name of the sales_rep in each region with the largest amount of total_amt_usd sales.
   
   

2. For the region with the largest sales total_amt_usd, how many total orders were placed?
   
   

3. How many accounts had more total purchases than the account name which has bought the most standard_qty paper throughout their lifetime as a customer?
   
   

4. For the customer that spent the most (in total over their lifetime as a customer) total_amt_usd, how many web_events did they have for each channel?
   
   

5. What is the lifetime average amount spent in terms of total_amt_usd for the top 10 total spending accounts?
   
   

6. What is the lifetime average amount spent in terms of total_amt_usd, including only the companies that spent more per order, on average, than the average of all orders.